﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace App.Data
{
    public class AppSQL
    {
        public const String GET_PROJECT_COUNT = @"
                SELECT COUNT(P.ID) 
                FROM Project P
                    INNER JOIN Customer C ON C.ID = P.CustomerID
                    INNER JOIN PackageSubscription PS ON PS.ID = P.PackageSubscriptionID                      
                WHERE P.PropertyTypeID = {0} 
                    AND P.IsDeleted = 0
                    AND C.IsApproved = 1
			        AND C.IsDeleted = 0
                    AND PS.IsActiveSubscription = 1
                    AND PS.ExpireDate >= GETDATE()";
        public const String GET_PAGED_ACTIVE_CONTACTS = @"
                            WITH Contacts AS
                            (
                                SELECT Contact.[ID]
		                            , Contact.[Name]		                            
		                            , Designation.[Name] AS Designation
		                            , Contact.[Phone]
		                            , Contact.[Fax]
		                            , Contact.[Email]       
		                            , ROW_NUMBER() OVER(ORDER BY Contact.[Name]) AS RowNumber	
	                            FROM Contact 
		                            INNER JOIN Designation ON Designation.ID = Contact.DesignationID	
	                            WHERE Contact.CustomerID = @CustomerID
	                                AND Contact.IsDeleted = 0
                            )
                            SELECT ID
                                , Name                                
                                , Designation
	                            , Phone
                                , Fax
                                , Email
                                , (SELECT COUNT(*) FROM Contacts) AS TotalRecord
                            FROM Contacts
                            WHERE RowNumber BETWEEN {0} AND {1}";
        public const String GET_PAGED_NEWS_LIST = @"WITH Newses AS
                            (
                                SELECT ID
                                    , Title
                                    , NewsContent AS [News]
                                    , Image1
                                    , PublishedOn
                                    , ROW_NUMBER() OVER(ORDER BY PublishedOn DESC) AS RowNumber	
                                FROM News         
                                WHERE IsDeleted = 0
                            )
                            SELECT ID
                                , Title
                                , News
                                , Image1
                                , PublishedOn
                                , (SELECT COUNT(*) FROM Newses) AS TotalRecord
                            FROM Newses
                            WHERE RowNumber BETWEEN {0} AND {1}";
        
        public const String GET_PAGED_ARTICLE_LIST = @"
                            WITH Articles AS
                            (
                                SELECT ID
                                    , Title
                                    , ArticleContent AS [Article]
                                    , Image1
                                    , PublishedOn
                                    , ROW_NUMBER() OVER(ORDER BY PublishedOn DESC) AS RowNumber	
                                FROM Article         
                                WHERE IsDeleted = 0
                            )
                            SELECT ID
                                , Title
                                , Article
                                , Image1
                                , PublishedOn
                                , (SELECT COUNT(*) FROM Articles) AS TotalRecord
                            FROM Articles
                            WHERE RowNumber BETWEEN {0} AND {1}";
        public const String GET_PAGED_PROJECTS_BY_CUSTOMER = @"WITH Projects AS
                                (
	                                SELECT Project.ID 
		                                , Project.Name
		                                , Project.Description
		                                , Project.ShowPricingInfo
		                                , ProjectStatus.Name AS [Status]
                                        , PropertyType.Name AS [PropertyType]
		                                , City.Name AS City 
		                                , Zone.Name AS Zone 
                                        , IsHot As [Is Featured]
		                                , ROW_NUMBER() OVER (ORDER BY Project.Name) AS RowNumber
	                                FROM Project
                                        INNER JOIN PropertyType ON PropertyType.ID = Project.PropertyTypeID
		                                INNER JOIN ProjectStatus ON ProjectStatus.ID = Project.ProjectStatusID
		                                INNER JOIN Zone ON Zone.ID = Project.ZoneID
		                                INNER JOIN City ON City.ID = Zone.CityID
	                                WHERE Project.CustomerID = @CustomerID
                                        AND Project.IsDeleted = 0
                                )
                                SELECT *
	                                , (SELECT COUNT(*) FROM Projects) AS TotalRecord
                                FROM Projects
                                WHERE RowNumber BETWEEN {0} AND {1}";

        public const String GET_PACKAGE_LIST = @"
                        SELECT P.ID
	                        , P.Name
	                        , P.Price
	                        , C.Symbol AS Currency
	                        , P.Duration
	                        , P.ProductLimit
	                        , P.IsActive 
                        FROM dbo.Package P
	                        INNER JOIN Currency C ON C.ID = P.CurrencyID";
        public const String GET_ACTIVE_PACKAGE_LIST = @"
                        SELECT Package.ID
	                        , Package.Name	
	                        , Package.Duration
	                        , Package.ProductLimit
	                        , Package.Price
	                        , Currency.Symbol AS [Currency Symbol]
                        FROM dbo.Package
	                        INNER JOIN Currency ON Currency.ID = Package.CurrencyID
                        WHERE Package.IsActive = 1";

        public const String GET_USER_FAVORITES = @"SELECT P.ID	                        
	                        , P.Name AS [Project Title]	
                            , T.Name AS [Property Type]
                            , C.Name AS Company 	                        
                        FROM Favorite F
                        INNER JOIN Project P ON P.ID = F.ProjectID
                        INNER JOIN Customer C ON C.ID = P.CustomerID
                        INNER JOIN PropertyType T ON T.ID = P.PropertyTypeID
                        WHERE F.UserID = @UserID";

        public const String GET_CUSTOMER_CONTACTS = @"
                        SELECT C.ID 
	                        , C.Name
	                        , C.Photo
	                        , C.Phone
	                        , C.Fax
	                        , C.Email
	                        , D.Name AS Designation
                        FROM Contact C
                        INNER JOIN Designation D ON D.ID = C.DesignationID
                        WHERE CustomerID = @CustomerID
                            AND C.IsDeleted = 0
                        ORDER BY D.Grade";

        public const String GET_ACTIVER_CUSTOMER_COUNT = "SELECT COUNT(ID) FROM Customer WHERE IsApproved = 1 AND IsDeleted = 0";

        public const String PAGED_ADVERTISEMENT_LIST_FOR_ADMIN = @"                        
                        WITH Advertisements AS
                        (
	                        SELECT A.ID	 
                                    , C.Name AS [Customer Name]
                                    , A.[Name]
                                    , A.[FileType]
                                    , A.[FileName]
                                    , A.[SortOrder] AS [Sort Order]
                                    , A.[IsActive]
                                    , A.[Position]
                                    , A.[IsHomePageBanner] AS [Homepage Banner?] 
		                        , ROW_NUMBER() OVER (ORDER BY A.Created DESC) AS RowNumber
                            FROM Advertisement A
                                INNER JOIN Customer C ON C.ID = A.CustomerID
                            WHERE C.IsApproved = 1
                            AND C.IsDeleted = 0
                            AND A.IsDeleted = 0
                        )
                        SELECT *
	                        , (SELECT MAX(RowNumber) FROM Advertisements) AS TotalRecord 
                        FROM Advertisements
                        WHERE RowNumber BETWEEN {0} AND {1} ";

        public const String GET_ADVERTISEMENTS = @"
                        SELECT TOP 8 * FROM Advertisement
                        WHERE IsActive = 1 
                            AND IsDeleted = 0 
                            AND Position = @Position
                        ORDER BY SortOrder";
        public const String GET_FEATURED_ADVERTISEMENTS = @"
                        SELECT * FROM Advertisement
                        WHERE IsActive = 1 
                            AND IsDeleted = 0 
                            AND IsFeatured = 1
                        ORDER BY SortOrder";

        public const String GET_PROPERTY_FOR_RENT_BY_CUSTOMER = @"
                        WITH PropertyForRents AS
                        (
	                        SELECT PTR.ID
		                        , PTR.RentTypeID
		                        , PTR.RentStatusID
		                        , C.Name AS [CompanyName]
		                        , PTR.Size
		                        , PTR.NoOfBeds
                                , PTR.Address     
		                        , PTR.MonthlyRent
                                , City.Name AS [City]		                        
                                , Zone.Name AS [Zone]
		                        , PTR.ServiceCharge		                        
		                        , PTR.ExpireDate
		                        , PTR.ThumbImage
		                        , PTR.LargeImage
		                        , RT.Name AS [RentType]
		                        , RS.Name AS [RentStatus]		                        
		                        , Country.Name AS [Country] 
		                        , ROW_NUMBER() OVER (ORDER BY PTR.Created DESC) AS RowNumber
	                        FROM PropertyToRent PTR
		                        INNER JOIN Customer C ON C.ID = PTR.CompanyID
		                        INNER JOIN RentType RT ON RT.ID = PTR.RentTypeID
		                        INNER JOIN RentStatus RS ON RS.ID = PTR.RentStatusID
		                        INNER JOIN Zone ON Zone.ID = PTR.ZoneID
		                        INNER JOIN City ON City.ID = Zone.CityID
		                        INNER JOIN Country ON Country.ID = City.CountryID		
	                        WHERE C.ID = @CustomerID
		                        AND PTR.IsDeleted = 0 
                        )
                        SELECT *
	                        , (SELECT MAX(RowNumber) FROM PropertyForRents) AS TotalRecord  
                        FROM PropertyForRents
                        WHERE RowNumber BETWEEN {0} AND {1}";

        public const String GET_PAGED_PROPERTIES_FOR_RENT = @"
                        WITH PropertyForRents AS
                        (
                            SELECT PTR.ID
                                , Address.Phone
		                        , Address.Fax
		                        , Address.Email                                
                                , C.Name AS [CompanyName]
                                , PTR.Size
                                , PTR.NoOfBeds
                                , PTR.Address     
                                , PTR.MonthlyRent
                                , City.Name AS [City]		                        
                                , Zone.Name AS [Zone]
                                , PTR.ServiceCharge		                        
                                , PTR.ExpireDate
                                , PTR.ThumbImage
                                , PTR.LargeImage
                                , PTR.Remark
                                , RT.Name AS [RentType]
                                , RS.Name AS [RentStatus]		                        
                                , Country.Name AS [Country] 
                                , ROW_NUMBER() OVER (ORDER BY PTR.RentStatusID, PTR.Created DESC) AS RowNumber
                            FROM PropertyToRent PTR
                                INNER JOIN Customer C ON C.ID = PTR.CompanyID
                                INNER JOIN RentType RT ON RT.ID = PTR.RentTypeID
                                INNER JOIN RentStatus RS ON RS.ID = PTR.RentStatusID
                                INNER JOIN Zone ON Zone.ID = PTR.ZoneID
                                INNER JOIN City ON City.ID = Zone.CityID
                                INNER JOIN Country ON Country.ID = City.CountryID
                                LEFT JOIN Address ON Address.CustomerID = C.ID		
                            WHERE PTR.IsDeleted = 0
		                        AND PTR.IsActive = 1
		                        AND PTR.ExpireDate >= GETDATE() 
                        )
                        SELECT *
                            , (SELECT MAX(RowNumber) FROM PropertyForRents) AS TotalRecord  
                        FROM PropertyForRents
                        WHERE RowNumber BETWEEN {0} AND {1}";

        public const String GET_PAGED_EVENTS = @"
                        WITH AllEvents AS
                        (
                            SELECT [ID]
                                , Title
		                        , [Description]
                                , [EventDate]
                                , [ImageFile]
                                , [ThumbImageFile]
		                        , [EventDate] AS [Date]  
                                , Venue  
		                        , ROW_NUMBER() OVER(ORDER BY ID DESC) AS RowNumber	
                            FROM [Event]                                      
                        )
                        SELECT [ID]
                            , Title
	                        , [Description]
                            , [EventDate]
                            , [ImageFile]
                            , [ThumbImageFile]
	                        , [Date] 
                            , Venue
                            , (SELECT MAX(RowNumber) FROM AllEvents) AS TotalRecord
                        FROM AllEvents
                        WHERE RowNumber BETWEEN {0} AND {1}";
        public const String GET_PAGED_ALL_EVENTS = @"
                        WITH AllEvents AS
                        (
                            SELECT [ID]
                                , Title
		                        , [Description]
                                , [ImageFile]
                                , [ThumbImageFile]
		                        , [EventDate]    
                                , Venue
		                        , ROW_NUMBER() OVER(ORDER BY EventDate DESC) AS RowNumber	
                            FROM [Event] 
                            --WHERE EventDate >= GETDATE()            
                        )
                        SELECT [ID]
                            , Title
	                        , [Description]
                            , [ImageFile]
                            , [ThumbImageFile]
	                        , [EventDate]
                            , Venue 
                            , (SELECT MAX(RowNumber) FROM AllEvents) AS TotalRecord
                        FROM AllEvents
                        WHERE RowNumber BETWEEN {0} AND {1}";

        public const String GET_BOOKINGS_BY_EVENT_ID = @"
                        WITH AllEventBooking AS
                        (
                            SELECT [ID]
                                , Email
                                , [Name]
                                , [Address] 
		                        , [ContactNo]	
                                , [Created] AS [Submitted ON]   
                                , ROW_NUMBER() OVER(ORDER BY Created DESC) AS RowNumber	
                            FROM [EventBooking]
                            WHERE EventID = @EventID
                        )
                        SELECT [ID]
                            , Email
                            , [Name]
                            , [Address] 
	                        , [ContactNo]	   
                            , [Submitted ON]
                            , (SELECT MAX(RowNumber) FROM AllEventBooking) AS TotalRecord
                        FROM AllEventBooking
                        WHERE RowNumber BETWEEN {0} AND {1}";
        public const String GET_ZONE_WISE_PROJECT_COUNT_BY_COUNTRY = @"
                        WITH ZoneWiseProjects AS
                        (
                            SELECT City.ID AS CityID 
                                , City.Name AS City 
	                            , Zone.ID AS ZoneID
                                , Zone.Name AS Zone	
	                            , (
		                            SELECT COUNT(Project.ID) 
		                            FROM Project 
			                            INNER JOIN PackageSubscription PS ON PS.ID = PackageSubscriptionID
                                        INNER JOIN Customer C ON C.ID = Project.CustomerID
		                            WHERE ZoneID = Zone.ID 
                                        AND C.IsApproved = 1
			                            AND C.IsDeleted = 0
                                        AND Project.PropertyTypeID = @PropertyTypeID
			                            AND Project.IsDeleted = 0
                                        AND PS.IsActiveSubscription = 1 
			                            AND PS.ExpireDate >= GETDATE()
	                               ) AS ProjectCount
                            FROM Zone
	                            INNER JOIN City ON City.ID = Zone.CityID
	                            INNER JOIN Country ON Country.ID = City.CountryID
                            WHERE Country.ID = @CountryID
                        )
                        SELECT * FROM ZoneWiseProjects 
                        --WHERE ProjectCount > 0
                        ORDER BY City, Zone";

        public const string GET_CONTENT_LIST = @"
                        WITH ContentList AS
                        (
	                        SELECT 
		                        ID
		                        , Title
		                        , Description
		                        , Modified
		                        , ROW_NUMBER() OVER (ORDER BY ID ) AS RowNumber			
	                        FROM Content
                        )
                        SELECT ID
		                    , Title
		                    , Description
		                    , Modified 
	                        , (SELECT MAX(RowNumber) FROM ContentList) AS TotalRecord
                        FROM ContentList
                        WHERE RowNumber BETWEEN {0} AND {1}";
        public const String GET_PAGED_SPECIAL_OFFERS = @"
                        WITH SubmittedSpecialOffers AS
                        (
                            SELECT *
                                , ROW_NUMBER() OVER(ORDER BY Created DESC) AS RowNumber	
                            FROM SpecialOffer         
                            WHERE HasPublished = 0
                        )
                        SELECT *
                            , (SELECT MAX(RowNumber) FROM SubmittedSpecialOffers) AS TotalRecord
                        FROM SubmittedSpecialOffers
                        WHERE RowNumber BETWEEN {0} AND {1}";

        public const String GET_NEWSLETTER_LIST = @"
                        WITH NewsLetters AS
                        (
	                        SELECT *
		                        , ROW_NUMBER() OVER (ORDER BY ID DESC) AS RowNumber  
	                        FROM Newsletter 	
                        )
                        SELECT *
                            , (SELECT MAX(RowNumber) FROM NewsLetters) AS TotalRecord
                        FROM NewsLetters
                        WHERE RowNumber BETWEEN {0} AND {1}";

        public const String GET_PAGED_USERS_BY_CUSTOMER = @"
                        WITH Users AS
                        (
	                        SELECT U.ID
                                , U.CustomerID
		                        , U.LoginName AS [User Name]
		                        , U.IsActive AS [Is Active?]                                 
		                        , Contact.Name AS [Contact Name]
		                        , ROW_NUMBER() OVER (ORDER BY U.LoginName) AS RowNumber
	                        FROM [User] U
		                        INNER JOIN Contact ON Contact.ID = U.ContactID
		                        INNER JOIN Customer C ON C.ID = Contact.CustomerID
	                        WHERE C.ID = @CustomerID
		                        AND Contact.IsDeleted = 0
                                AND U.IsDeleted = 0    	    
                        )
                        SELECT *
                            , (SELECT MAX(RowNumber) FROM Users) AS TotalRecord
                        FROM Users
                        WHERE RowNumber BETWEEN {0} AND {1}";

        public static String GetPagedTestimonials(bool filterPublished, bool hasPublished)
        {
            StringBuilder SQL = new StringBuilder(@"
                            WITH Testimonials AS
                            (
                                SELECT Testimonial.[ID]
                                    , Testimonial.Title
                                    , Testimonial.Description
                                    , Testimonial.ThumbImage
                                    , Testimonial.Image
                                    , Testimonial.PublishDate
                                    , Testimonial.Modified
                                    , Testimonial.Created        
                                    , ROW_NUMBER() OVER(ORDER BY Testimonial.PublishDate DESC) AS RowNumber	
                                FROM Testimonial ");
            if(filterPublished)
                SQL.AppendFormat("{0}WHERE HasPublished = {1}{0}", Environment.NewLine, hasPublished ? "1" : "0");

            SQL.Append(@"
                            )
                            SELECT ID
                                , Title                                
                                , Description
                                , ThumbImage
                                , Image
                                , PublishDate
                                , Modified
                                , Created
                                , (SELECT MAX(RowNumber) FROM Testimonials) AS TotalRecord
                            FROM Testimonials
                            WHERE RowNumber BETWEEN {0} AND {1}");
            return SQL.ToString();
        }


        public static String GetPagedMediaReports(bool filterPublished, bool hasPublished)
        {
            StringBuilder SQL = new StringBuilder(@"
                            WITH MediaReports AS
                            (
                                SELECT MediaReport.[ID]
                                    , MediaReport.Title
                                    , MediaReport.Description
                                    , MediaReport.ThumbImage
                                    , MediaReport.Image
                                    , MediaReport.ReceiveDate
                                    , MediaReport.Modified
                                    , MediaReport.Created        
                                    , ROW_NUMBER() OVER(ORDER BY MediaReport.ReceiveDate DESC) AS RowNumber	
                                FROM MediaReport ");
            if(filterPublished)
                SQL.AppendFormat("{0}WHERE HasPublished = {1}{0}", Environment.NewLine, hasPublished ? "1" : "0");
            
            SQL.Append(@"
                            )
                            SELECT ID
                                , Title                                
                                , Description
                                , ThumbImage
                                , Image
                                , ReceiveDate
                                , Modified
                                , Created
                                , (SELECT MAX(RowNumber) FROM MediaReports) AS TotalRecord
                            FROM MediaReports
                            WHERE RowNumber BETWEEN {0} AND {1}");
            return SQL.ToString();
        }

        public const String GET_JOINT_VENTURE_LIST_BY_TYPE = @"
                        WITH JointVentures AS
                        (
	                        SELECT *
		                        , ROW_NUMBER() OVER (ORDER BY Created DESC) AS RowNumber  
	                        FROM JointVenture 	
                            WHERE VentureType = @VentureType
                        )
                        SELECT *
                            , (SELECT MAX(RowNumber) FROM JointVentures) AS TotalRecord
                        FROM JointVentures
                        WHERE RowNumber BETWEEN {0} AND {1}";

        public const String GET_BASICINFO_INDIVIDUAL_USER = @"SELECT C.ID	                        
	                        , C.Name AS [Name]	
                            , C.Address AS [Address]
                            , C.ZipCode AS ZipCode
                            , C.Phone AS Phone 
                            , C.Comment AS Comment 	                        
                        FROM Customer C
                        
                        WHERE C.ID = @CustomerID";

        public const String GET_PROPERTYTYPE_BY_CUSTOMER = @"SELECT P.ID	                        
	                        , P.Name 	                        
                            FROM PropertyType P 
                            INNER JOIN CustomerPropertyType CPT ON CPT.PropertyTypeID=P.ID                        
                            WHERE P.BusinessTypeID = @BusinessTypeID
                            AND CPT.CustomerID=@CustomerID";

        public const String GET_ZONES_BY_CUSTOMER = @"SELECT P.ID	                        
	                        , P.Name, P.CityID  	                        
                            FROM Zone P 
                            INNER JOIN CustomerPreferredZone CPZ ON CPZ.ZoneID=P.ID                        
                            WHERE CPZ.CustomerID=@CustomerID";

        public const String GET_PAGED_CUSTOMERS_BY_SUBSCRIPTION = @"WITH CustomersBySubscriptions AS
                                (
	                                SELECT C.ID AS CustomerID
		                                , C.Name AS [Customer Name]
		                                , PK.Name AS [Package Name]
		                                , PS.StartDate AS [Start Date]
                                        , PK.Duration AS [No of days]
                                        , PS.ID AS SubscriptionID
                                        , PS.IsActiveSubscription AS [Is Active?]    
		                                , PS.ExpireDate AS [Expire Date]
                                        , PS.ProductCount AS [Product Count]		                                 
		                                , ROW_NUMBER() OVER (ORDER BY C.Name) AS RowNumber
	                                FROM Customer C
                                        INNER JOIN PackageSubscription PS ON C.ID = PS.CustomerID
		                                INNER JOIN Package PK ON PK.ID = PS.PackageID		                                
	                                WHERE C.IsDeleted = 0
                                        AND C.IsIndividual = 0                                        
                                )
                                SELECT *
	                                , (SELECT COUNT(*) FROM CustomersBySubscriptions) AS TotalRecord
                                FROM CustomersBySubscriptions
                                WHERE RowNumber BETWEEN {0} AND {1}";

        public const String GET_PAGED_CUSTOMERS = @"WITH Customers AS
                                (
	                                SELECT C.ID AS CustomerID
		                                , C.Name AS [Company Name]
		                                , C.BusinessDetails AS [Business Details]
                                        , C.Address AS [Address]
		                                , BT.ShortName AS [Business Type]                                      
                                        , Con.Email As Email
		                                , ROW_NUMBER() OVER (ORDER BY BT.ShortName) AS RowNumber
	                                FROM Customer C
                                        INNER JOIN BusinessType BT ON BT.ID = C.BusinessTypeID
                                        INNER JOIN Contact Con ON Con.CustomerID = C.ID
		                                 WHERE C.IsIndividual=0
                                         AND C.IsDeleted=0                             
	                                                                         
                                )
                                SELECT *
	                                , (SELECT COUNT(*) FROM Customers) AS TotalRecord
                                FROM Customers
                                WHERE RowNumber BETWEEN {0} AND {1}";

        public const String GET_PAGED_INDIVIDUAL_CUSTOMERS = @"WITH Customers AS
                                (
	                                SELECT C.ID AS CustomerID
		                                , C.Name AS [Customer Name]
		                                , C.Phone AS [Phone]
                                        , C.Address AS [Address]
		                                , C.ZipCode AS [Zip Code]                                      
                                        , U.LoginName AS Email
		                                , ROW_NUMBER() OVER (ORDER BY C.Name) AS RowNumber
	                                FROM Customer C
                                        INNER JOIN [User] U ON U.CustomerID=C.ID
		                                 WHERE C.IsIndividual=1
                                         AND C.IsDeleted=0                             
	                                                                         
                                )
                                SELECT *
	                                , (SELECT COUNT(*) FROM Customers) AS TotalRecord
                                FROM Customers
                                WHERE RowNumber BETWEEN {0} AND {1}";

        public const String GET_PACKAGE_DETAILS_BY_CUSTOMER = @"
                SELECT PK.Name AS Name,               
                PK.Duration AS Duration,
                PK.Price AS Price,
                PS.StartDate AS StartDate,
                PS.ExpireDate AS ExpireDate,
                PS.ProductCount AS ProductCount 
                    ,(SELECT COUNT(P.ID) AS Totalproject from Project P 
                    INNER JOIN PackageSubscription PS
                    ON P.PackageSubscriptionID = PS.ID
                    WHERE P.CustomerID=@CustomerID
                    AND P.IsDeleted=0 
                    AND PS.IsActiveSubscription=1) As ProjectSubmitted
                FROM Package PK 
                INNER JOIN PackageSubscription PS
                ON PK.ID = PS.PackageID 
                WHERE PS.CustomerID=@CustomerID
                AND PS.IsActiveSubscription=1";

        public const String GET_ACTIVE_WITHCOST_PACKAGE_LIST = @"
                        SELECT Package.ID
	                        , Package.Name	
	                        , Package.Duration
	                        , Package.ProductLimit
	                        , Package.Price
	                        , Currency.Symbol AS [Currency Symbol]
                        FROM dbo.Package
	                        INNER JOIN Currency ON Currency.ID = Package.CurrencyID
                        WHERE Package.IsActive = 1 AND Package.Price>0";

        public const String GET_PAGED_ACTIVE_SPECIAL_OFFERS_LIST = @"
                        WITH SpecialOffers AS
                        (
	                        SELECT SpecialOffer.ID
                                , SpecialOffer.Title
                                , SpecialOffer.Description
                                , SpecialOffer.Created
                                , SpecialOffer.PictureThumb
                                , SpecialOffer.CustomerID
                                , 'ProjectType' =
                                  CASE
                                    WHEN Customer.IsIndividual = 1 THEN (SELECT 'Rent -' + RentType.Name FROM RentType WHERE RentType.ID = SpecialOffer.ProjectType)
                                    ELSE (SELECT PropertyType.Name FROM PropertyType WHERE PropertyType.ID = SpecialOffer.ProjectType)
                                  END
                                , Customer.Name
                                , dbo.GetLocation(SpecialOffer.ZoneID) AS Location
                                , Customer.IsIndividual 
                                , ROW_NUMBER() OVER(ORDER BY SpecialOffer.Created DESC) AS RowNumber
                            FROM SpecialOffer
                            INNER JOIN Customer ON Customer.ID = SpecialOffer.CustomerID
                            WHERE SpecialOffer.HasPublished = 1
                        )
                        SELECT *
	                        , (SELECT MAX(RowNumber) FROM SpecialOffers) AS TotalRecord
                        FROM SpecialOffers
                        WHERE RowNumber BETWEEN {0} AND {1}";

        public const String GET_PAGED_SPECIAL_OFFERS_LIST = @"
                        WITH SpecialOffers AS
                        (
	                        SELECT SpecialOffer.ID
		                        , SpecialOffer.CustomerID
		                        , SpecialOffer.Title
		                        , SpecialOffer.Description
		                        , SpecialOffer.Created
                                , SpecialOffer.PictureThumb	
                                , 'ProjectType' =
                                  CASE
                                    WHEN Customer.IsIndividual = 1 THEN (SELECT 'Rent -' + RentType.Name FROM RentType WHERE RentType.ID = SpecialOffer.ProjectType)
                                    ELSE (SELECT PropertyType.Name FROM PropertyType WHERE PropertyType.ID = SpecialOffer.ProjectType)
                                  END 
		                        , Customer.Name
                                , dbo.GetLocation(SpecialOffer.ZoneID) AS Location    
                                , Customer.IsIndividual 
		                        , ROW_NUMBER() OVER(ORDER BY SpecialOffer.Created DESC) AS RowNumber
	                        FROM SpecialOffer
	                        INNER JOIN Customer ON Customer.ID = SpecialOffer.CustomerID
	                        --WHERE HasPublished = 1
                        )
                        SELECT *
	                        , (SELECT MAX(RowNumber) FROM SpecialOffers) AS TotalRecord
                        FROM SpecialOffers
                        WHERE RowNumber BETWEEN {0} AND {1}";

        public const String GET_PAGED_SPECIAL_OFFERS_BY_CUSTOMER = @"
                        WITH SpecialOffers AS
                        (
                            SELECT SpecialOffer.ID
                                , SpecialOffer.Title
                                , SpecialOffer.Description
                                , SpecialOffer.Created
                                , SpecialOffer.PictureThumb
                                , SpecialOffer.CustomerID
                                , 'ProjectType' =
		                          CASE
			                        WHEN Customer.IsIndividual = 1 THEN (SELECT 'Rent -' + RentType.Name FROM RentType WHERE RentType.ID = SpecialOffer.ProjectType)
			                        ELSE (SELECT PropertyType.Name FROM PropertyType WHERE PropertyType.ID = SpecialOffer.ProjectType)
		                          END
                                , '' AS [Name]	
                                , dbo.GetLocation(SpecialOffer.ZoneID) AS Location 
                                , Customer.IsIndividual 
                                , ROW_NUMBER() OVER(ORDER BY SpecialOffer.Created DESC) AS RowNumber
                            FROM SpecialOffer
                            INNER JOIN Customer ON Customer.ID = SpecialOffer.CustomerID
                            WHERE SpecialOffer.CustomerID = @CustomerID
                        )
                        SELECT *
                            , (SELECT MAX(RowNumber) FROM SpecialOffers) AS TotalRecord
                        FROM SpecialOffers
                        WHERE RowNumber BETWEEN {0} AND {1}";
                            
    }
}
